<?php

namespace Home\Service;

use Home\Common\FIdConst;

/**
 * Portal Service
 *
 * @author 李静波
 */
class PortalService extends PSIBaseService {

	public function inventoryPortal() {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		
		$result = array();
		
		$db = M();
		$sql = "select id, name 
				from t_warehouse 
				where (inited = 1) ";
		$queryParams = array();
		$ds = new DataOrgService();
		$rs = $ds->buildSQL(FIdConst::PORTAL_INVENTORY, "t_warehouse");
		if ($rs) {
			$sql .= " and " . $rs[0];
			$queryParams = $rs[1];
		}
		
		$sql .= " order by code ";
		$data = $db->query($sql, $queryParams);
		foreach ( $data as $i => $v ) {
			$result[$i]["warehouseName"] = $v["name"];
			$warehouseId = $v["id"];
			
			/**
			// 库存金额
			$sql = "select sum(balance_money) as balance_money 
					from t_inventory
					where warehouse_id = '%s' ";
			$d = $db->query($sql, $warehouseId);
			if ($d) {
				$m = $d[0]["balance_money"];
				$result[$i]["inventoryMoney"] = $m ? $m : 0;
			} else {
				$result[$i]["inventoryMoney"] = 0;
			}
			
			
			// 低于安全库存数量的商品种类
			$sql = "select count(*) as cnt
					from t_inventory i, t_goods_si s
					where i.goods_id = s.goods_id and i.warehouse_id = s.warehouse_id
						and s.safety_inventory > i.balance_count
						and i.warehouse_id = '%s' ";
			$d = $db->query($sql, $warehouseId);
			$result[$i]["siCount"] = $d[0]["cnt"];
			
			// 超过库存上限的商品种类
			$sql = "select count(*) as cnt
					from t_inventory i, t_goods_si s
					where i.goods_id = s.goods_id and i.warehouse_id = s.warehouse_id
						and s.inventory_upper < i.balance_count 
						and (s.inventory_upper <> 0 and s.inventory_upper is not null)
						and i.warehouse_id = '%s' ";
			$d = $db->query($sql, $warehouseId);
			$result[$i]["iuCount"] = $d[0]["cnt"];
			**/
			
			// 库存商品吨数
			$sql = "select sum(balance_count) as balance_count
						from t_inventory i
						where 1 = 1
							and i.warehouse_id = '%s' ";
			$d = $db->query($sql, $warehouseId);
			$result[$i]["balance_count"] = $d[0]["balance_count"];
		}
		
		return $result;
	}

	/**
	 * 销售出库看板
	 */
	public function salePortal() {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		
		$result = array();
		
		$db = M();
		
		// 当月
		$sql = "select year(now()) as y, month(now()) as m";
		$data = $db->query($sql);
		$year = $data[0]["y"];
		$month = $data[0]["m"];
		
		for($i = 0; $i < 6; $i ++) {
			if ($month < 10) {
				$result[$i]["month"] = "$year-0$month";
			} else {
				$result[$i]["month"] = "$year-$month";
			}
			
			$sql = "select sum(b.goods_count) as goods_count from 
						t_ws_bill w ,t_ws_bill_detail b 
					where 
						w.id = b.wsbill_id 
					    and w.bill_status = 1000
						and year(w.bizdt) = %d
						and month(w.bizdt) = %d";
			$queryParams = array();
			$queryParams[] = $year;
			$queryParams[] = $month;
			$ds = new DataOrgService();
			$rs = $ds->buildSQL(FIdConst::PORTAL_SALE, "w");
			if ($rs) {
				$sql .= " and " . $rs[0];
				$queryParams = array_merge($queryParams, $rs[1]);
			}
			
			$data = $db->query($sql, $queryParams);
			$goods_count = $data[0]["goods_count"];
			if (! $goods_count) {
				$goods_count = 0;
			}
			$profit = $data[0]["profit"];
			if (! $profit) {
				$profit = 0;
			}
			
			// 扣除退货
			$sql = "select sum(b.rejection_goods_count) as rej_goods_count from 
						t_sr_bill w ,t_sr_bill_detail b 
					where 
						w.id = b.srbill_id 
						and w.bill_status = 1000
						and year(w.bizdt) = %d
						and month(w.bizdt) = %d";
			$queryParams = array();
			$queryParams[] = $year;
			$queryParams[] = $month;
			$ds = new DataOrgService();
			$rs = $ds->buildSQL(FIdConst::PORTAL_SALE, "w");
			if ($rs) {
				$sql .= " and " . $rs[0];
				$queryParams = array_merge($queryParams, $rs[1]);
			}
			
			$data = $db->query($sql, $queryParams);
			$rej_goods_count = $data[0]["rej_goods_count"];
			if (! $rej_goods_count) {
				$rej_goods_count = 0;
			}
			
			$goods_count -= $rej_goods_count;
			
			$result[$i]["goods_count"] = $goods_count;
			
			if ($goods_count != 0) {
				$result[$i]["rate"] = sprintf("%0.2f", $profit / $goods_count * 100) . "%";
			} else {
				$result[$i]["rate"] = "";
			}
			
			// 获得上个月
			if ($month == 1) {
				$month = 12;
				$year -= 1;
			} else {
				$month -= 1;
			}
		}
		
		return $result;
	}

	/**
	 * 采购入库看板
	 */
	public function purchasePortal() {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		
		$result = array();
		
		$db = M();
		
		// 当月
		$sql = "select year(now()) as y, month(now()) as m";
		$data = $db->query($sql);
		$year = $data[0]["y"];
		$month = $data[0]["m"];
		
		for($i = 0; $i < 6; $i ++) {
			if ($month < 10) {
				$result[$i]["month"] = "$year-0$month";
			} else {
				$result[$i]["month"] = "$year-$month";
			}
			
			$sql = "select sum(b.goods_count) as goods_count
					from t_pw_bill w, t_pw_bill_detail b
					where w.bill_status = 1000 and w.id = b.pwbill_id
						and year(w.biz_dt) = %d
						and month(w.biz_dt) = %d";
			$queryParams = array();
			$queryParams[] = $year;
			$queryParams[] = $month;
			$ds = new DataOrgService();
			$rs = $ds->buildSQL(FIdConst::PORTAL_PURCHASE, "w");
			if ($rs) {
				$sql .= " and " . $rs[0];
				$queryParams = array_merge($queryParams, $rs[1]);
			}
			
			$data = $db->query($sql, $queryParams);
			$goods_count = $data[0]["goods_count"];
			if (! $goods_count) {
				$goods_count = 0;
			}
			
			// 扣除退货
			$sql = "select sum(b.rejection_goods_count) as rej_goods_count
					from t_pr_bill s,t_pr_bill_detail b
					where s.bill_status = 1000 and s.id = b.prbill_id
						and year(s.bizdt) = %d
						and month(s.bizdt) = %d";
			$queryParams = array();
			$queryParams[] = $year;
			$queryParams[] = $month;
			$ds = new DataOrgService();
			$rs = $ds->buildSQL(FIdConst::PORTAL_PURCHASE, "s");
			if ($rs) {
				$sql .= " and " . $rs[0];
				$queryParams = array_merge($queryParams, $rs[1]);
			}
			
			$data = $db->query($sql, $queryParams);
			$rej_goods_count = $data[0]["rej_goods_count"];
			if (! $rej_goods_count) {
				$rej_goods_count = 0;
			}
			
			$goods_count -= $rej_goods_count;
			
			$result[$i]["goods_count"] = $goods_count;
			
			// 获得上个月
			if ($month == 1) {
				$month = 12;
				$year -= 1;
			} else {
				$month -= 1;
			}
		}
		
		return $result;
	}
	
	/**
	 * 每日出入库明细看板
	 */
	public function dailyDetailPortal() {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
	
		$result = array();
	
		$db = M();
	
		$sql = "select id, name
				from t_warehouse
				where (inited = 1) ";
		$queryParams = array();
		$ds = new DataOrgService();
		$rs = $ds->buildSQL(FIdConst::PORTAL_INVENTORY, "t_warehouse");
		if ($rs) {
			$sql .= " and " . $rs[0];
			$queryParams = $rs[1];
		}
		
		$sql .= " order by code ";
		$data = $db->query($sql, $queryParams);
		
		$resultCount = 0;
		foreach ( $data as $i => $v ) {  //遍历仓库
			$result[$resultCount]["warehouseName"] = $v["name"];
			$warehouseId = $v["id"];
	
			for($j = 0; $j < 6; $j ++) { //每个仓库6天
				
				$sql = "select sum(b.goods_count) as goods_count
					from t_pw_bill w, t_pw_bill_detail b
					where w.bill_status = 1000 and w.id = b.pwbill_id 
							and w.warehouse_id = '%s'
							and w.biz_dt = DATE_SUB(CURRENT_DATE(),INTERVAL ". $j ." DAY)";
				$queryParams = array();
				$queryParams[0] = $warehouseId;
				$ds = new DataOrgService();
				$rs = $ds->buildSQL(FIdConst::PORTAL_PURCHASE, "w");
				if ($rs) {
					$sql .= " and " . $rs[0];
					$queryParams = array_merge($queryParams, $rs[1]);
				}
			
				$data = $db->query($sql, $queryParams);
				$goods_count = $data[0]["goods_count"];
				if (! $goods_count) {
					$goods_count = 0;
				}
			
				// 扣除退货
				$sql = "select sum(b.rejection_goods_count) as rej_goods_count
					from t_pr_bill s,t_pr_bill_detail b
					where s.bill_status = 1000 and s.id = b.prbill_id
							and s.warehouse_id = '%s'
							and s.bizdt = DATE_SUB(CURRENT_DATE(),INTERVAL ". $j ." DAY)";
				$queryParams = array();
				$queryParams[0] = $warehouseId;
				$ds = new DataOrgService();
				$rs = $ds->buildSQL(FIdConst::PORTAL_PURCHASE, "s");
				if ($rs) {
					$sql .= " and " . $rs[0];
					$queryParams = array_merge($queryParams, $rs[1]);
				}
					
				$data = $db->query($sql, $queryParams);
				$rej_goods_count = $data[0]["rej_goods_count"];
				if (! $rej_goods_count) {
					$rej_goods_count = 0;
				}
			
				$goods_count -= $rej_goods_count;
			
				$result[$resultCount]["in_count"] = $goods_count;
				
				
				
				/***********上面是出库*************/
				$sql = "select sum(b.goods_count) as goods_count
					from t_ws_bill w, t_ws_bill_detail b
					where w.bill_status = 1000 and w.id = b.wsbill_id
							and w.warehouse_id = '%s'
							and w.bizdt = DATE_SUB(CURRENT_DATE(),INTERVAL ". $j ." DAY)";
				$queryParams = array();
				$queryParams[0] = $warehouseId;
				$ds = new DataOrgService();
				$rs = $ds->buildSQL(FIdConst::PORTAL_PURCHASE, "w");
				if ($rs) {
					$sql .= " and " . $rs[0];
					$queryParams = array_merge($queryParams, $rs[1]);
				}
					
				$data = $db->query($sql, $queryParams);
				$goods_count = $data[0]["goods_count"];
				if (! $goods_count) {
					$goods_count = 0;
				}
					
				// 扣除退货
				$sql = "select sum(b.rejection_goods_count) as rej_goods_count
					from t_sr_bill s,t_sr_bill_detail b
					where s.bill_status = 1000 and s.id = b.srbill_id
							and s.warehouse_id = '%s'
							and s.bizdt = DATE_SUB(CURRENT_DATE(),INTERVAL ". $j ." DAY)";
				$queryParams = array();
				$queryParams[0] = $warehouseId;
				$ds = new DataOrgService();
				$rs = $ds->buildSQL(FIdConst::PORTAL_PURCHASE, "s");
				if ($rs) {
					$sql .= " and " . $rs[0];
					$queryParams = array_merge($queryParams, $rs[1]);
				}
					
				$data = $db->query($sql, $queryParams);
				$rej_goods_count = $data[0]["rej_goods_count"];
				if (! $rej_goods_count) {
					$rej_goods_count = 0;
				}
					
				$goods_count -= $rej_goods_count;
					
				$result[$resultCount]["out_count"] = $goods_count;
				
				
				/***********上面是入库*************/
				
				
				/***************汇总*****************/
				$result[$resultCount]["sum"] = $result[$resultCount]["out_count"] +  $result[$resultCount]["in_count"];
				
				
				
				$result[$resultCount]["date"] = date("Y-m-d",strtotime('-' . $j . ' day'));
				
				$resultCount++;
			}
			
		}
	
		return $result;
	}
	
	/**
	 * 每月出入库明细看板
	 */
	public function monthDetailPortal() {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		
		$result = array();
		
		$db = M();
		
		
		$sql = "select id, name
				from t_warehouse
				where (inited = 1) ";
		$queryParams = array();
		$ds = new DataOrgService();
		$rs = $ds->buildSQL(FIdConst::PORTAL_INVENTORY, "t_warehouse");
		if ($rs) {
			$sql .= " and " . $rs[0];
			$queryParams = $rs[1];
		}
		
		$sql .= " order by code ";
		$data = $db->query($sql, $queryParams);
		
		$resultCount = 0;
		foreach ( $data as $i => $v ) {  //遍历仓库
			$result[$resultCount]["warehouseName"] = $v["name"];
			$warehouseId = $v["id"];
			
			// 当月
			$sql = "select year(now()) as y, month(now()) as m";
			$data = $db->query($sql);
			$year = $data[0]["y"];
			$month = $data[0]["m"];
			
			for($j = 0; $j < 6; $j ++) {
				if ($month < 10) {
					$result[$resultCount]["month"] = "$year-0$month";
				} else {
					$result[$resultCount]["month"] = "$year-$month";
				}
				
				$sql = "select sum(b.goods_count) as goods_count from 
							t_ws_bill w ,t_ws_bill_detail b 
						where 
							w.id = b.wsbill_id 
							and w.warehouse_id = '%s'
						    and w.bill_status = 1000
							and year(w.bizdt) = %d
							and month(w.bizdt) = %d";
				$queryParams = array();
				$queryParams[] = $warehouseId;
				$queryParams[] = $year;
				$queryParams[] = $month;
				$ds = new DataOrgService();
				$rs = $ds->buildSQL(FIdConst::PORTAL_SALE, "w");
				if ($rs) {
					$sql .= " and " . $rs[0];
					$queryParams = array_merge($queryParams, $rs[1]);
				}
				
				$data = $db->query($sql, $queryParams);
				$goods_count = $data[0]["goods_count"];
				if (! $goods_count) {
					$goods_count = 0;
				}
				$profit = $data[0]["profit"];
				if (! $profit) {
					$profit = 0;
				}
				
				// 扣除退货
				$sql = "select sum(b.rejection_goods_count) as rej_goods_count from 
							t_sr_bill w ,t_sr_bill_detail b 
						where 
							w.id = b.srbill_id 
							and w.warehouse_id = '%s'
							and w.bill_status = 1000
							and year(w.bizdt) = %d
							and month(w.bizdt) = %d";
				$queryParams = array();
				$queryParams[] = $warehouseId;
				$queryParams[] = $year;
				$queryParams[] = $month;
				$ds = new DataOrgService();
				$rs = $ds->buildSQL(FIdConst::PORTAL_SALE, "w");
				if ($rs) {
					$sql .= " and " . $rs[0];
					$queryParams = array_merge($queryParams, $rs[1]);
				}
				
				$data = $db->query($sql, $queryParams);
				$rej_goods_count = $data[0]["rej_goods_count"];
				if (! $rej_goods_count) {
					$rej_goods_count = 0;
				}
				
				$goods_count -= $rej_goods_count;
				
				$result[$resultCount]["out_count"] = $goods_count;
	
				/****************上面是出库*****************/
	
				
				$sql = "select sum(b.goods_count) as goods_count
						from t_pw_bill w, t_pw_bill_detail b
						where w.bill_status = 1000 and w.id = b.pwbill_id
							and w.warehouse_id = '%s'
							and year(w.biz_dt) = %d
							and month(w.biz_dt) = %d";
				$queryParams = array();
				$queryParams[] = $warehouseId;
				$queryParams[] = $year;
				$queryParams[] = $month;
				$ds = new DataOrgService();
				$rs = $ds->buildSQL(FIdConst::PORTAL_PURCHASE, "w");
				if ($rs) {
					$sql .= " and " . $rs[0];
					$queryParams = array_merge($queryParams, $rs[1]);
				}
					
				$data = $db->query($sql, $queryParams);
				$goods_count = $data[0]["goods_count"];
				if (! $goods_count) {
					$goods_count = 0;
				}
					
				// 扣除退货
				$sql = "select sum(b.rejection_goods_count) as rej_goods_count
						from t_pr_bill s,t_pr_bill_detail b
						where s.bill_status = 1000 and s.id = b.prbill_id
							and s.warehouse_id = '%s'
							and year(s.bizdt) = %d
							and month(s.bizdt) = %d";
				$queryParams = array();
				$queryParams[] = $warehouseId;
				$queryParams[] = $year;
				$queryParams[] = $month;
				$ds = new DataOrgService();
				$rs = $ds->buildSQL(FIdConst::PORTAL_PURCHASE, "s");
				if ($rs) {
					$sql .= " and " . $rs[0];
					$queryParams = array_merge($queryParams, $rs[1]);
				}
					
				$data = $db->query($sql, $queryParams);
				$rej_goods_count = $data[0]["rej_goods_count"];
				if (! $rej_goods_count) {
					$rej_goods_count = 0;
				}
					
				$goods_count -= $rej_goods_count;
					
				$result[$resultCount]["in_count"] = $goods_count;
				
				
				/****************上面是入库*****************/
				
				//合计
				$result[$resultCount]["sum"] = $result[$resultCount]["out_count"] + $result[$resultCount]["in_count"]; 
				
				$resultCount++;
				
				// 获得上个月
				if ($month == 1) {
					$month = 12;
					$year -= 1;
				} else {
					$month -= 1;
				}
			}
		}
		return $result;
	}

	public function moneyPortal() {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		
		$result = array();
		
		$db = M();
		$us = new UserService();
		$companyId = $us->getCompanyId();
		
		// 应收账款
		$result[0]["item"] = "应收账款";
		$sql = "select sum(balance_money) as balance_money
				from t_receivables 
				where company_id = '%s' ";
		$data = $db->query($sql, $companyId);
		$balance = $data[0]["balance_money"];
		if (! $balance) {
			$balance = 0;
		}
		$result[0]["balanceMoney"] = $balance;
		
		// 账龄30天内
		$sql = "select sum(balance_money) as balance_money
				from t_receivables_detail
				where datediff(current_date(), biz_date) < 30
					and company_id = '%s' ";
		$data = $db->query($sql, $companyId);
		$balance = $data[0]["balance_money"];
		if (! $balance) {
			$balance = 0;
		}
		$result[0]["money30"] = $balance;
		
		// 账龄30-60天
		$sql = "select sum(balance_money) as balance_money
				from t_receivables_detail
				where datediff(current_date(), biz_date) <= 60
					and datediff(current_date(), biz_date) >= 30
					and company_id = '%s' ";
		$data = $db->query($sql, $companyId);
		$balance = $data[0]["balance_money"];
		if (! $balance) {
			$balance = 0;
		}
		$result[0]["money30to60"] = $balance;
		
		// 账龄60-90天
		$sql = "select sum(balance_money) as balance_money
				from t_receivables_detail
				where datediff(current_date(), biz_date) <= 90
					and datediff(current_date(), biz_date) > 60
					and company_id = '%s' ";
		$data = $db->query($sql, $companyId);
		$balance = $data[0]["balance_money"];
		if (! $balance) {
			$balance = 0;
		}
		$result[0]["money60to90"] = $balance;
		
		// 账龄大于90天
		$sql = "select sum(balance_money) as balance_money
				from t_receivables_detail
				where datediff(current_date(), biz_date) > 90
					and company_id = '%s' ";
		$data = $db->query($sql, $companyId);
		$balance = $data[0]["balance_money"];
		if (! $balance) {
			$balance = 0;
		}
		$result[0]["money90"] = $balance;
		
		// 应付账款
		$result[1]["item"] = "应付账款";
		$sql = "select sum(balance_money) as balance_money
				from t_payables 
				where company_id = '%s' ";
		$data = $db->query($sql, $companyId);
		$balance = $data[0]["balance_money"];
		if (! $balance) {
			$balance = 0;
		}
		$result[1]["balanceMoney"] = $balance;
		
		// 账龄30天内
		$sql = "select sum(balance_money) as balance_money
				from t_payables_detail
				where datediff(current_date(), biz_date) < 30
					and company_id = '%s' ";
		$data = $db->query($sql, $companyId);
		$balance = $data[0]["balance_money"];
		if (! $balance) {
			$balance = 0;
		}
		$result[1]["money30"] = $balance;
		
		// 账龄30-60天
		$sql = "select sum(balance_money) as balance_money
				from t_payables_detail
				where datediff(current_date(), biz_date) <= 60
					and datediff(current_date(), biz_date) >= 30
					and company_id = '%s' ";
		$data = $db->query($sql, $companyId);
		$balance = $data[0]["balance_money"];
		if (! $balance) {
			$balance = 0;
		}
		$result[1]["money30to60"] = $balance;
		
		// 账龄60-90天
		$sql = "select sum(balance_money) as balance_money
				from t_payables_detail
				where datediff(current_date(), biz_date) <= 90
					and datediff(current_date(), biz_date) > 60
					and company_id = '%s' ";
		$data = $db->query($sql, $companyId);
		$balance = $data[0]["balance_money"];
		if (! $balance) {
			$balance = 0;
		}
		$result[1]["money60to90"] = $balance;
		
		// 账龄大于90天
		$sql = "select sum(balance_money) as balance_money
				from t_payables_detail
				where datediff(current_date(), biz_date) > 90
					and company_id = '%s' ";
		$data = $db->query($sql, $companyId);
		$balance = $data[0]["balance_money"];
		if (! $balance) {
			$balance = 0;
		}
		$result[1]["money90"] = $balance;
		
		return $result;
	}
}